<?php

class SqlEngine {

	public static function getUser($filters = array()) {
		$pdo_params = array();

		$query = "SELECT *
                    FROM user
                   WHERE 1 = 1";

		if (!empty($filters['id'])) {
			$query .= " AND user_id = :id";
			$pdo_params['id'] = $filters['id'];
		}

		if (isset($filters['password'])) {
			$query .= " AND password = md5(:password)";
			$pdo_params['password'] = $filters['password'];
		}

		if (isset($filters['username'])) {
			$query .= " AND username = :username";
			$pdo_params['username'] = $filters['username'];
		}

		if (isset($filters['user_type'])) {
			$query .= " AND user_type = :user_type";
			$pdo_params['user_type'] = $filters['user_type'];
		}

		return SqlEngineCore::get()->executeSql($query, $pdo_params);
	}

	public static function getRestaurants($filters = array()) {
		$pdo_params = array();

		$query = "SELECT *
                    FROM restaurant
                   WHERE 1 = 1";

		if (!empty($filters['name'])) {
			$query .= " AND name = :name";
			$pdo_params['name'] = $filters['name'];
		}

		if (!empty($filters['restaurant_id'])) {
			$query .= " AND restaurant_id = :restaurant_id";
			$pdo_params['restaurant_id'] = $filters['restaurant_id'];
		}

		if (!empty($filters['name_like'])) {
			$query .= " AND name LIKE :name_like";
			$pdo_params['name_like'] = '%' . $filters['name_like'] . '%';
		}

		return SqlEngineCore::get()->executeSql($query, $pdo_params);
	}

	/**
	 *  Get restaurant id based on the reservation id
	 * @param int  $reservation_id
	 * @since  29.04.2012
	 * @user  Kristian
	 */
	public static function getRestaurantsfromReservationId($reservation_id) {
		$pdo_params = array();

		$query = "SELECT restaurant_id
                    FROM reservation
                   WHERE reservation_id = :reservation_id";
		$pdo_params['reservation_id'] =$reservation_id;	
		$result=SqlEngineCore::get()->executeSql($query, $pdo_params);
		return $result[0]['restaurant_id'] ;
	}


	public static function getUserRestaurants($filters = array()) {
		$pdo_params = array();

		$query = "SELECT *
                    FROM restaurant
               LEFT JOIN user_restaurant ON user_restaurant.restaurant_id = restaurant.restaurant_id
                   WHERE 1 = 1";

		if (!empty($filters['name'])) {
			$query .= " AND name = :name";
			$pdo_params['name'] = $filters['name'];
		}

		if (!empty($filters['user_id'])) {
			$query .= " AND user_restaurant.user_id = :user_id";
			$pdo_params['user_id'] = $filters['user_id'];
		}

		if (!empty($filters['name_like'])) {
			$query .= " AND name LIKE :name_like";
			$pdo_params['name_like'] = '%' . $filters['name_like'] . '%';
		}

		return SqlEngineCore::get()->executeSql($query, $pdo_params);
	}

	public static function getConsoleReservations($filters = array()) {
		$pdo_params = array();

		$query = "SELECT reservation.*,
                         (SELECT MAX(created_at) FROM reservation) last_created_at
                    FROM reservation
               LEFT JOIN console_log ON (
                                console_log.reservation_id = reservation.reservation_id
                            AND console_log.restaurant_id = reservation.restaurant_id
                            AND console_log.user_id = :user_id)
                   WHERE console_log.reservation_id IS NULL
                     AND reservation.restaurant_id = :restaurant_id
                     AND reservation.date >= date(NOW())";

		$pdo_params['restaurant_id'] = $filters['restaurant_id'];
		$pdo_params['user_id'] = $filters['user_id'];

		if (!empty($filters['after_created_at'])) {
			$query .= " AND reservation.created_at > :after_created_at";
			$pdo_params['after_created_at'] = $filters['after_created_at'];
		}

		$query .= " ORDER BY reservation.created_at ASC";
		//var_dump($filters);
		//var_dump($query);
		return SqlEngineCore::get()->executeSql($query, $pdo_params);
	}

	public static function getReservations($filters = array()) {
		$pdo_params = array();

		$query = "SELECT *, (SELECT MAX(created_at) FROM reservation) last_created_at
                    FROM reservation
                   WHERE 1 = 1";

		if (!empty($filters['not_deleted'])) {
			$query .= " AND deleted_at IS NULL";
		}

		if (!empty($filters['today_and_after'])) {
			$query .= " AND DATE(date) >= date(NOW())";
		}

		if (!empty($filters['reservation_id'])) {
			$query .= " AND reservation_id = :reservation_id";
			$pdo_params['reservation_id'] = $filters['reservation_id'];
		}

		if (!empty($filters['restaurant_id'])) {
			$query .= " AND restaurant_id = :restaurant_id";
			$pdo_params['restaurant_id'] = $filters['restaurant_id'];
		}

		if (!empty($filters['token'])) {
			$query .= " AND token = :token";
			$pdo_params['token'] = $filters['token'];
		}

		if (!empty($filters['time'])) {
			$query .= " AND TIME(time) =TIME( :time)";
			$pdo_params['time'] = $filters['time'];
		}

		if (!empty($filters['time_between'])) {
			$query .= " AND TIME(time) >=TIME(:time_start) AND TIME(time) <= TIME(:time_end)";
			$pdo_params['time_start'] = $filters['time_between'][0];
			$pdo_params['time_end'] = $filters['time_between'][1];
		}

		if (!empty($filters['date'])) {
			$query .= " AND date = :date";
			$pdo_params['date'] = $filters['date'];
		}

		if (!empty($filters['after_created_at'])) {
			$query .= " AND created_at > :after_created_at";
			$pdo_params['after_created_at'] = $filters['after_created_at'];
		}

		if (!empty($filters['order']))
		$query .= " ORDER BY " . $filters['order'][0] . ' ' . $filters['order'][1];
		else
		$query .= " ORDER BY time";
		//echo $query;
		//var_dump($filters);
		return SqlEngineCore::get()->executeSql($query, $pdo_params);
	}
	
	public static function getDayIntervalsInfo($filters = array()) {
		$pdo_params = array();

		$query = "SELECT *
                    FROM settings_time_schedule
                   WHERE day = :day
                     AND restaurant_id = :restaurant_id";

		if (!empty($filters['time'])) {
			$query .= " AND TIME(:time1) >= TIME(time_start) AND TIME(:time2) <=TIME(time_end)";
			$pdo_params['time1'] = $filters['time'];
			$pdo_params['time2'] = $filters['time'];
		}

		$query .= " ORDER BY str_to_date(time_start,'%k:%i')";

		$pdo_params['restaurant_id'] = $filters['restaurant_id'];
		$pdo_params['day'] = $filters['day'];
		//        echo $query;
		//        var_dump($filters);
		return SqlEngineCore::get()->executeSql($query, $pdo_params);
	}

	public static function getReservationTimes($filters = array()) {
		$query = "SELECT time
                    FROM reservation
                   WHERE TIME(time) >= TIME(:time_start)
                     AND TIME(time) < TIME(:time_end)
                     AND DATE(date) = DATE(:date)
                     AND restaurant_id = :restaurant_id
                     AND deleted_at IS NULL
                   GROUP BY time";

		return SqlEngineCore::get()->executeSql($query, $filters);
	}

	/**
	 * Get null or message if user try to make a reservation after the allowed time for reservation,or reservation group
	 * @param unknown_type $filters
	 * @since  08.06.2012
	 * @user  Kristian
	 */
	public static function getMsgForExpiredTimeReservation($filters = array()) {
		$pdo_params=array();
		$query = "SELECT	CASE WHEN (`group_size`<=:person_no1  AND (NOW()<TIMESTAMPADD(HOUR,`reservation_min_time`,TIMESTAMP(:time_now))))
						THEN `reservation_min_time_msg` 
						WHEN (`group_size` >:person_no2  AND (NOW()<TIMESTAMPADD(HOUR,`reservation_grp_min_time`,TIMESTAMP(:time_now))))
						THEN `reservation_grp_min_time_msg`
            ELSE NULL
            END 
						as expired_msg FROM `settings`
						WHERE `restaurant_id` =:restaurant_id";
		if (!empty($filters['person_no'])) {
			$pdo_params['person_no1'] = $filters['person_no'];
			$pdo_params['person_no2'] = $filters['person_no'];
		}
		
		if (!empty($filters['restaurant_id'])) {
			$pdo_params['restaurant_id'] = $filters['restaurant_id'];
		}
		
		if ((!empty($filters['date'])) &&(!empty($filters['time']))) {
			$pdo_params['time_now']=strtotime($filters['date']." ".$filters['time']);
		}else{
			$pdo_params['time_now']=time();
		}
		return SqlEngineCore::get()->executeSql($query, $pdo_params);
		
	}
	

	public static function getSpecialDates($filters = array()) {
		$pdo_params = array();

		$query = "SELECT *
                    FROM settings_special_date
                   WHERE DATE(date) >= DATE(NOW())";

		if (!empty($filters['restaurant_id'])) {
			$query .= " AND restaurant_id = :restaurant_id";
			$pdo_params['restaurant_id'] = $filters['restaurant_id'];
		}

		if (!empty($filters['date_end'])) {
			$query .= " AND date < = :date_end";
			$pdo_params['date_end'] = $filters['date_end'];
		}
		
		$query .= " ORDER BY date ASC";

		if (!empty($filters['count'])) {
			$query .= " LIMIT " . (int) $filters['count'];
		}

		return SqlEngineCore::get()->executeSql($query, $pdo_params);
	}

	public static function getSettings($filters = array()) {
		$query = "SELECT *
                    FROM settings
                   WHERE restaurant_id = :restaurant_id";

		return SqlEngineCore::get()->executeSql($query, $filters, true);
	}

	public static function getTemplates($filters = array()) {
		$pdo_params = array();

		$query = "SELECT *
                    FROM template
                   WHERE 1 = 1";

		if (!empty($filters['category_id'])) {
			$query .= " AND template_category_id = :category_id";
			$pdo_params['category_id'] = $filters['category_id'];
		}

		if (!empty($filters['type_id'])) {
			$query .= " AND template_type_id = :type_id";
			$pdo_params['type_id'] = $filters['type_id'];
		}

		return SqlEngineCore::get()->executeSql($query, $pdo_params);
	}

	public static function getTemplateTypes($filters = array()) {
		$pdo_params = array();

		$query = "SELECT *
                    FROM template_type
                   WHERE 1 = 1";

		if (!empty($filters['name'])) {
			$query .= " AND name = :name";
			$pdo_params['name'] = $filters['name'];
		}

		return SqlEngineCore::get()->executeSql($query, $pdo_params);
	}

	public static function getTemplateCategory($filters = array()) {
		$pdo_params = array();

		$query = "SELECT *
                    FROM template_category
                   WHERE 1 = 1";

		if (!empty($filters['name'])) {
			$query .= " AND name = :name";
			$pdo_params['name'] = $filters['name'];
		}

		return SqlEngineCore::get()->executeSql($query, $pdo_params);
	}

	public static function getMenus($filters = array()) {
		$pdo_params = array();

		$query = "SELECT *
                    FROM menu
                   WHERE 1 = 1";

		if (!empty($filters['restaurant_id'])) {
			$query .= " AND restaurant_id = :restaurant_id";
			$pdo_params['restaurant_id'] = $filters['restaurant_id'];
		}

		if (!empty($filters['menu_id'])) {
			$query .= " AND menu_id = :menu_id";
			$pdo_params['menu_id'] = $filters['menu_id'];
		}

		return SqlEngineCore::get()->executeSql($query, $pdo_params);
	}

	public static function getBlockTimes($filters = array()) {
		$pdo_params = array();

		$query = "SELECT *
                    FROM settings_dead_time
                   WHERE 1 = 1";

		if (!empty($filters['restaurant_id'])) {
			$query .= " AND restaurant_id = :restaurant_id";
			$pdo_params['restaurant_id'] = $filters['restaurant_id'];
		}

		if (!empty($filters['date'])) {
			$query .= " AND DATE(:date1) >= DATE(date_start) AND DATE(:date2) <= DATE(date_end)";
			$pdo_params['date1'] = $filters['date'];
			$pdo_params['date2'] = $filters['date'];
		}

		if (!empty($filters['time'])) {
			$query .= " AND TIME(:time1) >= TIME(time_start) AND TIME(:time2) <=TIME(time_end)";
			$pdo_params['time1'] = $filters['time'];
			$pdo_params['time2'] = $filters['time'];
		}

		$query .= " ORDER BY date_start ASC, time_start ASC";
		//echo $query;
		//var_dump($filters);
		return SqlEngineCore::get()->executeSql($query, $pdo_params);
	}
	

	public static function getReservationLogs($reservationId = null) {
		$pdo_params = array();

		$query = "SELECT *
                    FROM reservation_log
                   WHERE 1 = 1";

		if (null != $reservationId) {
			$query .= " AND reservation_id = :reservation_id";
			$pdo_params['reservation_id'] = $reservationId;
		}

		return SqlEngineCore::get()->executeSql($query, $pdo_params);
	}
}
